Recommended Indexes

Recommended indexes are the descriptors used by CData Virtuality Server to create physical indexes in the materialized tables. This dialog allows to manage index recommendations for the selected optimization. It allows to:

• Set index creation policy for the optimization.
• Setup Amazon Redshift specific options.
Amazon Redshift specific options are supported for Redshift Analytical Storage only.
• Add and remove recommended indexes.
• Enable and disable recommended indexes.

Index creation policy

CData Virtuality Server will create physical indexes according to the optimization settings. The settings are as following:

• New and enabled: will materialize only enabled and new recommended indexes.
• Enabled: will materialize only enabled recommended indexes.
• None: no physical indexes will be created.
• Default: using server global settings (stored in 'INDEX_CREATION_POLICY' property).

Redshift Distribution Style

When data is loaded into a table, Amazon Redshift distributes the rows of the table to each of the node slices according to the table's distribution style. Possible distribution styles are as follows:

• EVEN: The data in the table is spread evenly across the nodes in a cluster in a round-robin distribution. Row IDs are used to determine the distribution, and roughly the same number of rows are distributed to each node. This distribution method is the default.
• KEY: The data is distributed by the values in the DISTKEY column. When you set the joining columns of joining tables as distribution keys, the joining rows from both tables are collocated on the compute nodes. When data is collocated, the optimizer can perform joins more efficiently. If you specify DISTSTYLE KEY, you must name a DISTKEY column, either for the table or as part of the column definition.
• ALL: A copy of the entire table is distributed to every node. This distribution style ensures that all the rows required for any join are available on every node, but it multiplies storage requirements and increases the load and maintenance times for the table. ALL distribution can improve execution time when used with certain dimension tables where KEY distribution is not appropriate, but performance improvements must be weighed against maintenance costs.

Recommended Index List

Recommended indexes have the following fields:

• Kind: describes the type of the expressions:
  • Single column index
  • Multi column index
  • Expression-based
  Note: expression based indexes are not supported by MySQL or MSSQL Analytical Storage databases.
  Note: expressions should use fully qualified column names.
  • 'Distkey' (proprietary Redshift indexes)
  Note: 'Distkey' based indexes are only supported by Redshift Analytical Storage databases.
• Expression: the actual column, composition of columns or expression used to build the physical index
  Note: only new indexes are editable. Existing indexes can be removed.
• Type: the context of the query that offered this index recommendation:
  • Join - column is the join condition.
  • Where - column is the part of 'WHERE' clause.
  • Manual - is manually created index.
• Status: it is used by the index creation policy to identify if the index should be created or not:
  • New
  • Enabled
  • Disabled
• Frequency: the importance of the index (higher value, higher importance).

To create a new index:

1. Click on the add index button on the toolbar. It will generate an empty index definition with no column.
2. Select source table column in the dropdown list.